package com.jwb.pub.serviceImpl;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jwb.pub.dao.BaseDAO;
import com.jwb.pub.model.JavaModel;
import com.jwb.pub.service.UserInfoService;
import com.jwb.util.CommonUtil;
import com.jwb.util.Md5Str;
import com.jwb.util.ToolsUtil;

@Service

public class UserInfoServiceImpl implements UserInfoService {
	@Autowired
	private BaseDAO baseDAO;
	@Override
	public JSONObject checkLogin(String user, String pwd) {
		JSONObject jsobject = new JSONObject();
		String sql="SELECT password FROM user_info WHERE user_name='"+user+"' and sts = 'A'";
		try {
			jsobject =baseDAO.findBySqlToJosn(sql);
			if(jsobject.getJSONArray("list").size()<=0){
				
				jsobject.put("respCode", "-1");
				jsobject.put("respDesc", "用户不存在");			
			
			}else if(!pwd.equals(jsobject.getJSONArray("list").getJSONObject(0).getString("password"))) {
	
				jsobject.put("respCode", "-2");
				jsobject.put("respDesc", "密码不正确");		
			}else{
				jsobject.put("respCode", "0");
				jsobject.put("respDesc", "OK");	
			}	
			
		} catch (Exception e) {			
			jsobject.put("respCode", "9");
			jsobject.put("respDesc", "数据异常");		
			return jsobject;
		}
		
		return jsobject;
	}
	
	
	public JSONObject getUser(JSONObject jsdata){
		JSONObject jsobject = new JSONObject();
		try
      	{      		 
			    String sqlWhere="";
      		    String sqlOrder="";
      		    String sql="SELECT user_id, "+
						   "    real_name, "+
						   "    contact_phone,"+
						   "    cur_dept_id,"+
						   "    (SELECT dept_name"+
						   "       FROM dept"+
						   "      WHERE dept_id = cur_dept_id"+
						  "         AND dept_sts = 'A') cur_dept_name,"+
						 "      oper_dept_id,"+
						 "      (SELECT dept_name"+
						 "         FROM dept"+
						 "        WHERE dept_id = oper_dept_id"+
						"           AND dept_sts = 'A') oper_dept_name,"+
						"       role_list,"+
						"       (SELECT dept_level"+
						"          FROM dept"+
						"         WHERE dept_id = cur_dept_id"+
						"           AND dept_sts = 'A') cur_dept_level,"+
						"       (SELECT dept_level"+
						"          FROM dept"+
						"         WHERE dept_id = oper_dept_id"+
						"           AND dept_sts = 'A') oper_dept_level,"+
						"       u.rank,"+
						"       dept_code,"+
						"       group_code,"+
						"       CASE"+
						"         WHEN u.rank = 'rank_1' THEN"+
						"          (SELECT dept_id"+
						"             FROM dept"+
						"            WHERE dept_sts = 'A'"+
						"              AND dept_level = 1)"+
						"         WHEN u.rank = 'rank_2' THEN"+
						"          CASE"+
						"            WHEN (SELECT dept_level"+
						"                    FROM dept"+
						"                   WHERE dept_id = cur_dept_id"+
						"                     AND dept_sts = 'A') = 1 THEN"+
						"             (SELECT dept_id"+
						"                FROM dept"+
						"               WHERE dept_sts = 'A'"+
						"                 AND dept_level = 1)"+
						"            WHEN (SELECT dept_level"+
						"                    FROM dept"+
						"                   WHERE dept_id = cur_dept_id"+
						"                     AND dept_sts = 'A') = 2 THEN"+
      		    		"              cur_dept_id"+
      		    		"             WHEN (SELECT dept_level"+
      		    		"                     FROM dept"+
      		    		"                    WHERE dept_id = cur_dept_id"+
      		    		"                      AND dept_sts = 'A') = 3 THEN"+
      		    		"              (SELECT d.dept_parent_id"+
      		    		"                 FROM dept d"+
      		    		"                WHERE d.dept_sts = 'A'"+
      		    		"                  AND d.dept_id = cur_dept_id)"+
      		    		"           END"+
      		    		"          WHEN u.rank = 'rank_3' THEN"+
      		    		"           CASE"+
      		    		"             WHEN (SELECT dept_level"+
      		    		"                     FROM dept"+
      		    		"                    WHERE dept_id = cur_dept_id"+
      		    		"                      AND dept_sts = 'A') = 1 THEN"+
      		    		"              cur_dept_id"+
      		    		"             WHEN (SELECT dept_level"+
      		    		"                     FROM dept"+
      		    		"                    WHERE dept_id = cur_dept_id"+
      		    		"                      AND dept_sts = 'A') = 2 THEN"+
      		    		"              cur_dept_id"+
      		    		"             WHEN (SELECT dept_level"+
      		    		"                     FROM dept"+
      		    		"                    WHERE dept_id = cur_dept_id"+
      		    		"                      AND dept_sts = 'A') = 3 THEN"+
      		    		"              cur_dept_id"+
      		    		"           END"+
      		    		"          ELSE"+
      		    		"           CASE"+
      		    		"             WHEN (SELECT dept_level"+
      		    		"                     FROM dept"+
      		    		"                    WHERE dept_id = cur_dept_id"+
      		    		"                      AND dept_sts = 'A') = 1 THEN"+
      		    		"              cur_dept_id"+
      		    		"             WHEN (SELECT dept_level"+
      		    		"                     FROM dept"+
      		    		"                    WHERE dept_id = cur_dept_id"+
      		    		"                      AND dept_sts = 'A') = 2 THEN"+
      		    		"              cur_dept_id"+
      		    		"             WHEN (SELECT dept_level"+
      		    		"                     FROM dept"+
      		    		"                    WHERE dept_id = cur_dept_id"+
      		    		"                      AND dept_sts = 'A') = 3 THEN"+
      		    		"              cur_dept_id"+
      		    		"           END"+
      		    		"        END rank_dept_id,"+
      		    		"        kp_person,"+
      		    		"        ps_range"+
      		    		"   FROM user_info u "+
      		    		"  WHERE user_name = '"+jsdata.getString("name")+"'" +
      		    		"    AND sts = 'A'";
						
 		  
			    sqlOrder=" ";
			    sql+=sqlWhere+sqlOrder;	       
			    jsobject=baseDAO.findBySqlToJosn(sql);
      		
      	}   
      	catch(Exception e)
      	{
      		
      	}
      	
		return jsobject;

	}

	

	

	public String getRoleListNames(String roleList){
		StringBuilder listNames=new StringBuilder("");
		String[] roles = roleList.split(",");
		try{
			for(String r:roles){
				String sql="SELECT role_name from role where role_id='"+r+"'";
				JSONObject jso = baseDAO.findBySqlToJosn(sql).getJSONArray("list").getJSONObject(0);
				listNames.append(jso.getString("role_name"));
				listNames.append(",");
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		
		return listNames.toString().substring(0,listNames.length()-1);
	}
	
	
	

	
	
	
	@Override
	public JSONObject userlist(JSONObject jsdata) {
		JSONObject jsobject = new JSONObject();
		
		try
      	{      		 
			 
			String userId =jsdata.getString("userId");
			String rank =jsdata.getString("rank");
			String deptCode = jsdata.getString("deptCode");
			String groupCode = jsdata.getString("groupCode");
			String rankDeptId = jsdata.getString("rankDeptId");
			String privNames = jsdata.getString("privNames");
			
			 String sqlWhere="";
      		 String sqlOrder="";
			 String sql="select user_id, "+
//					   "    real_name, "+
"  u.real_name||'  '||t.gwmc||'('||d.dept_name||')' real_name,"+
					 "      dept_code, "+
					 "      (select dept_name "+
					"          from dept d "+
					"         where d.dept_sts = 'A' "+
					"           and d.dept_id = dept_code) dept_code_name, "+
				"	       group_code, "+
				"	       decode(group_code, "+
				"	              '0', "+
				"	              '无', "+
				"	              (select dept_name "+
				"	                 from dept d "+
				"	                where d.dept_sts = 'A' "+
				"	                  and d.dept_id = group_code)) group_code_name, "+
				   "    duty_flag "+
				"	  from  khgwpz t , user_info u,dept d"+
				"	 where  u.sts = 'A'"+
				"   and t.sts = 'A' and u.duty_code = t.gwid  "+
				"       and d.dept_sts='A'                 " +
				"         and u.cur_dept_id=d.dept_id              " +
				"                       " ; 

			     String personshow="";
				 if (jsdata.containsKey("personshow"))
				 {
					 personshow=jsdata.getString("personshow");
					 
				 }
				   //所有部门	
				    if("0".equals(personshow))
				    {
				    	sqlWhere+="";
				    }
				    //初评所有部门	
				    if("1".equals(personshow))
				    {
				    	sqlWhere += " and kp_person = '1'";
				    }
				    //终评所有部门	
				    else if("2".equals(personshow))
				    {
				    	sqlWhere += " and kp_person = '2' ";
				    	sqlWhere +="and dept_code in "+
					     "  (					   "+     
					  "      select dt.dept_id"+
					   "       from dept dt"+
					   "      where dept_sts = 'A'"+
					   "      start with dt.dept_id = (select s.dept_parent_id"+
					   "                                 from dept s"+
					   "                                where s.dept_sts = 'A'"+
					   "                                  and dept_id = '"+deptCode+"')"+
					   "     connect by prior dt.dept_id = dt.dept_parent_id)";
				    }
				    //本部门		
				    else if("3".equals(personshow))
				    {
				    	sqlWhere+="  and dept_code='"+deptCode+"'";
				    	
				    }
				    
				    //初评本部门			
				    else if("4".equals(personshow))
				    {
				    	 String gjly="";
				    	 if (jsdata.containsKey("gjly"))
						 {
				    		 gjly=jsdata.getString("gjly");
							 
						 }
			      		
				    	 if(!"".equals(deptCode)&&!"-1".equals(deptCode))
				    	 {
				    		 sqlWhere+="  and dept_code='"+deptCode+"'";
				    	 }
				    	
				    	sqlWhere += " and kp_person = '1'";
				    	
				    	if("-1".equals(gjly)||"".equals(gjly))
						 {
								
					     }
						 else
						 {
							 //sqlWhere +=" and (u.ps_range is null or  instr(','||u.ps_range||',', ','||'"+gjly+"'||',') > 0)"; 
							 sqlWhere +=" and (  instr(','||u.ps_range||',', ','||'"+gjly+"'||',') > 0)";     
						 }
				    	

				    }
				    
				    
				    //终评本部门				
				    else if("5".equals(personshow))
				    {
				    	sqlWhere+="  and dept_code='"+deptCode+"'";
				    	sqlWhere += " and kp_person = '2'";
				    }
				    
				    
				    //部门(rank决定)					
				    else if("6".equals(personshow))
				    {
				    	
				    	//二层所有部门
				    	if("rank_1".equals(rank))
				    	{
				    		sqlWhere+="";
				    	}
				    	//当前部门
				    	else if("rank_2".equals(rank)||"rank_3".equals(rank))
				    	{
				    		sqlWhere+="  and dept_code='"+deptCode+"'";
				    	}
				    				    	
				    }
				   //初评部门(rank决定)			
				    else if("7".equals(personshow))
				    {
				    	
				    	//rank="rank_3";
				    	//二层所有部门
				    	if("rank_1".equals(rank))
				    	{
				    		sqlWhere += " and kp_person = '1'";
				    	}
				    	//当前部门
				    	else if("rank_2".equals(rank)||"rank_3".equals(rank))
				    	{
				    		sqlWhere+="  and dept_code='"+deptCode+"'";
				    		sqlWhere += " and kp_person = '1'";
				    	}
				    				    	
				    }
				    
				    //终评部门(rank决定)			
				    else if("8".equals(personshow))
				    {
				    	
				    //rank="rank_3";
				    	//二层所有部门
				    	if("rank_1".equals(rank))
				    	{
				    		sqlWhere += " and kp_person = '2'";
				    	}
				    	//当前部门
				    	else if("rank_2".equals(rank)||"rank_3".equals(rank))
				    	{
				    		sqlWhere+="  and dept_code='"+deptCode+"'";
				    		sqlWhere += " and kp_person = '2'";
				    	}
				    				    	
				    }
				    
				    
				    else if("9".equals(personshow))
				    {
				    	
				    	
				    	sqlWhere += " and kp_person = '2' ";
				    	
				    	//leaf 叶子  0  非叶子 1
				    	String leaf="0";
				    	if (!jsdata.containsKey("leaf"))						
				    	{
				    		leaf="0";
				    	}
				    	else
				    	{
				    		leaf=jsdata.getString("leaf");
				    	}
				      if("0".equals(leaf))
				      {
				    	sqlWhere +="and dept_code in "+
					           "  (					   "+     
							   "      select dt.dept_id"+
							   "       from dept dt"+
							   "      where dept_sts = 'A'"+
							   "      start with dt.dept_id = (select s.dept_parent_id"+
							   "                                 from dept s"+
							   "                                where s.dept_sts = 'A'"+
							   "                                  and dept_id = '"+deptCode+"')"+
							   "     connect by prior dt.dept_id = dt.dept_parent_id)" ;
//				    	  sqlWhere=  "and d.dept_parent_id in (select s.dept_parent_id "+
//				    			  "from dept s "+
//				    			  " where s.dept_id = '"+deptCode+"' "+
//				    			  " and s.dept_sts = 'A') ";
				    	
				      }
				      else if ("1".equals(leaf))
				      {
				    	  sqlWhere += " and dept_code in "+
				         " (select dt.dept_id "+
				          "   from dept dt"+
				          "  where dept_sts = 'A'"+
				         "   start with dt.dept_id = '"+deptCode+"'"+
				         "  connect by prior dt.dept_id = dt.dept_parent_id)";
				    	
				      }
				      else
				      {
//				    	  sqlWhere +="and dept_code in "+
//						           "  (					   "+     
//								   "      select dt.dept_id"+
//								   "       from dept dt"+
//								   "      where dept_sts = 'A'"+
//								   "      start with dt.dept_id = (select s.dept_parent_id"+
//								   "                                 from dept s"+
//								   "                                where s.dept_sts = 'A'"+
//								   "                                  and dept_id = '"+deptCode+"')"+
//								   "     connect by prior dt.dept_id = dt.dept_parent_id)";
				      
				    	  sqlWhere=  "and d.dept_parent_id in (select s.dept_parent_id "+
				    			  "from dept s "+
				    			  " where s.dept_id = '"+deptCode+"' "+
				    			  " and s.dept_sts = 'A') ";
				      }	  
				      
				      String gjly="";
				    	 if (jsdata.containsKey("gjly"))
						 {
				    		 gjly=jsdata.getString("gjly");
							 
						 }
				    	 if("-1".equals(gjly)||"".equals(gjly))
						 {
								
					     }
						 else
						 {
							 //sqlWhere +=" and (u.ps_range is null or  instr(','||u.ps_range||',', ','||'"+gjly+"'||',') > 0)"; 
							 sqlWhere +=" and (  instr(','||u.ps_range||',', ','||'"+gjly+"'||',') > 0)";     
						 }
				    	
				    }
				    
				    else
				    {
				    	sqlWhere+="";
				    }
			   
			    
			    
			    		 
//			  
			    sqlOrder=" order by  real_name desc";
			    sql+=sqlWhere+sqlOrder;	       
			    jsobject=baseDAO.findBySqlToJosn(sql);
      		
      	}   
      	catch(Exception e)
      	{
      		
      	}
      	
		return jsobject;
	}
	

	public JSONArray loadTree(String parentId) {
		JSONArray array = new JSONArray();
		return queryTreeParent(parentId,array);
	}
	public JSONArray queryTreeParent(String parentId,JSONArray array){
	
		JSONObject obj = new JSONObject();
		JSONObject jsobject = new JSONObject();		
		String sql="SELECT id,label,pid,data_sign FROM v_dept_user where pid='"+parentId+"'   order by id";	
		try {
			jsobject=baseDAO.findBySqlToJosn(sql);
			if ("0".equals(jsobject.getString("respCode"))) {
				JSONArray list = jsobject.getJSONArray("list");
				for (int i = 0; i < list.size(); i++) {
					            obj = new JSONObject();			
						        obj.put("id", list.getJSONObject(i).getString("id"));						       
								obj.put("pid", list.getJSONObject(i).getString("pid"));
								if( list.getJSONObject(i).getString("data_sign").equals("p"))
								{
									obj.put("label", list.getJSONObject(i).getString("label")+"👱");
								}
								else
								{
									obj.put("label", list.getJSONObject(i).getString("label")+"");
								}
								obj.put("data_sign", list.getJSONObject(i).getString("data_sign"));
								JSONArray jsonarray=queryTreeParent(list.getJSONObject(i).getString("id"),new JSONArray());	
								if(jsonarray.size()>0)
								{
									obj.put("children", jsonarray);
								}
//								obj.put("id", list.getJSONObject(i).getString("id"));					
//								obj.put("pid", list.getJSONObject(i).getString("pid"));
//								if( list.getJSONObject(i).getString("data_sign").equals("p"))
//								{
//								 obj.put("label", list.getJSONObject(i).getString("label")+"👱");
//								}
//								else
//								{
//									obj.put("label", list.getJSONObject(i).getString("label")+"");
//								}
//								obj.put("data_sign", list.getJSONObject(i).getString("data_sign"));							
								array.add(obj);	   	
					
				}
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		
		return array;
	}
	
	
	public JSONObject getDeptUserInfo(String id) {
		JSONObject jsobject = new JSONObject();
		String sql = "SELECT id,label,pid,data_sign FROM v_dept_user where 1=1  ";
		if(!"".equals(id)){
			sql +=" and  id = '"+id+"'  ";  
		}
		try{
			jsobject=baseDAO.findBySqlToJosn(sql);
		}catch(Exception e){
			e.printStackTrace();
			jsobject.put("respCode", "-1");
			jsobject.put("respDesc", "数据访问失败");
		}
		return jsobject;
	}
	
	
	@Override
	public JSONObject importUsers(JSONObject jsdata){
		JSONObject jsobject = new JSONObject();
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "导入失败");
		String filePath = jsdata.getString("filePath");
		String oper_code = jsdata.getString("oper_code");
		String dept_id = jsdata.getString("dept_id");
		try{
			File file = new File(filePath);
			if(file.exists()&&file.isFile()){
				InputStream is = new FileInputStream(file);
				Workbook workbook = WorkbookFactory.create(is);
				Sheet sheet = workbook.getSheetAt(0);
				int rowCount = sheet.getPhysicalNumberOfRows();
				JSONArray excel = new JSONArray();
				List<String> names = new ArrayList<>();
				int col = 3;
				for (int r = 1; r < rowCount; r++) {
					Row row = sheet.getRow(r);
					if (row != null) {
						JSONObject jso = new JSONObject();
						jso.put("oper_code", oper_code);
						jso.put("dept_id", dept_id);
						for (int j = 1; j <= col; j++) { 
							switch(j){
								case 1:
									String name = CommonUtil.getCellValue(row.getCell(j - 1));
									jso.put("username", name);
									names.add(name);
									break;
								case 2:
									jso.put("realname", CommonUtil.getCellValue(row.getCell(j - 1)));
									break;
								case 3:
									jso.put("phone", CommonUtil.getCellValue(row.getCell(j - 1)));
									break;
								default:
									break;
							}				
						}
						excel.add(jso);		
					}
				}
				
				
				
				JSONArray success = new JSONArray();
				JSONArray failure = new JSONArray();
				if(excel.size()>0){
					for(int i=0;i<excel.size();i++){
						JSONObject item = excel.getJSONObject(i);
						if(doImport(item)){
							success.add(item);
						}else{
							failure.add(item);
						}						
					}				
				}			
				
				long cnt = names.stream().distinct().count();
				if(cnt<excel.size()){
					jsobject.put("respDesc", "存在相同登录名用户");
				}else{
					jsobject.put("respDesc","ok");
				}
				
				jsobject.put("success", success);
				jsobject.put("failure", failure);
				jsobject.put("respCode", "0");
			}			
		}catch(Exception e){
			e.printStackTrace();
		}
		return jsobject;
	}
	
	
	private boolean doImport(JSONObject obj){
		boolean done = true;
		try{
			String oper_code =obj.getString("oper_code");
			String dept_id = obj.getString("dept_id");
			String username = obj.getString("username");
			String realname = obj.getString("realname");
			String phone = obj.getString("phone");
			String sql = "SELECT COUNT(*) FROM user_info WHERE user_name='"+username+"' AND sts='A' ";
			ArrayList<JavaModel> list = new ArrayList<>();
			if(baseDAO.findBySqlCnt(sql)>0){
				sql = "UPDATE user_info SET real_name=?, contact_phone=?, cur_dept_id=?, phone=?, oper_code=?, oper_date=NOW(),dept_code=?  WHERE user_name = ? AND sts='A' ";				
				list.add(new JavaModel(realname));
				list.add(new JavaModel(phone));
				list.add(new JavaModel(Integer.valueOf(dept_id)));
				list.add(new JavaModel(phone));
				list.add(new JavaModel(oper_code));
				list.add(new JavaModel(Integer.valueOf(dept_id)));
				list.add(new JavaModel(username));
			}else{
				sql = "INSERT INTO user_info SET user_name=?, password=?, real_name=?, contact_phone=?, cur_dept_id=?, phone=?, oper_code=?, oper_date=NOW(),dept_code=?, role_list=? ";
				list.add(new JavaModel(username));
				list.add(new JavaModel(Md5Str.str2MD5to16byte(ToolsUtil.getProp("config.properties", "dft_pwd"))));
				list.add(new JavaModel(realname));
				list.add(new JavaModel(phone));
				list.add(new JavaModel(Integer.valueOf(dept_id)));
				list.add(new JavaModel(phone));
				list.add(new JavaModel(oper_code));
				list.add(new JavaModel(Integer.valueOf(dept_id)));
				list.add(new JavaModel(",6,"));
			}
			done = baseDAO.excuteSql(sql, list);			
		}catch(Exception e){
			e.printStackTrace();
			done = false;
		}		
		return done;
	}
	
	@Override
	public JSONObject delUser(String id){
		JSONObject jsobject= new JSONObject();
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "删除失败");
		try{
			if(!StringUtils.isEmpty(id)){
				String sql="UPDATE user_info SET sts='P' WHERE user_id=? and sts='A' ";
				ArrayList<JavaModel> list = new ArrayList<>();
				list.add(new JavaModel(id));
				if(baseDAO.excuteSql(sql,list)){
					jsobject.put("respCode", "0");
					jsobject.put("respDesc", "删除成功");
				}		
			}
		}catch(Exception e){
			e.printStackTrace();
		}		
		return jsobject;
	}
	
	
}
